using System;
using System.Data;
 

namespace UploadData
{
    class UploadSqlServerData 
    {
        public UploadSqlServerData()
        {
             
        }
        int uploadedRecords = 0;
        internal int UploadedRecords
        {
            get { return UploadedRecords; }
        }
        string connectionString = "";
        internal string ConnectionString
        {
            set { connectionString = value; }
            get { return connectionString; }
        }
        internal bool UploadDataIntoTable(DataTable dataTable)
        {
            uploadedRecords = 0;
            int totalRecords = dataTable.Rows.Count;
            if (totalRecords > 0) 
            {
                SqlServerProvider sqlServer =
                new SqlServerProvider();
                if (connectionString != "")
                    sqlServer.ConnectionString =
                    connectionString;
                int i = 0;
                while (i < totalRecords)
                {
                    i++;
                    if (dataTable.Rows[i][
                    "RecruiterID"].ToString() == "")
                        break;
                    sqlServer.CommandText = "SPInsertFromExcel";
                    sqlServer.CommandType = CommandType.StoredProcedure;
                    sqlServer.ParameterCollection = new string[26]{
                    "@RecruiterID", "@JobNo", "@JobTitle",
                    "@JobIndustryID", "@JobCategoryID", 
                    "@CertificateID", "@RangeOfAge", 
                    "@RecruitmentType", "@AdvName", 
                    "@ContactEmail", "@ContactPerson", 
                    "@ContactTel", "@ProvinceID", 
                    "@WorkLocation", "@ClosedDate", 
                    "@JobSummary", "@JobSkills", 
                    "@RequiredNumber", "@WorkingTypeID", 
                    "@ExperienceLevelID", "@YearExperience", 
                    "@SalaryFrom", "@SalaryTo", "@Currency", 
                    "@ShowSalary", "@SalaryNegotive"};
                    sqlServer.ValueCollection = new string[26]
                    {dataTable.Rows[i]["RecruiterID"].ToString(),
                    dataTable.Rows[i]["JobNo"].ToString(),
                    dataTable.Rows[i]["JobTitle"].ToString(),
                    dataTable.Rows[i]["JobIndustryID"].ToString(),
                    dataTable.Rows[i]["JobCategoryID"].ToString(),
                    dataTable.Rows[i]["CertificateID"].ToString(),
                    dataTable.Rows[i]["RangeOfAge"].ToString(),
                    dataTable.Rows[i]["RecruitmentType"].ToString(),
                    dataTable.Rows[i]["AdvName"].ToString(),
                    dataTable.Rows[i]["ContactEmail"].ToString(),
                    dataTable.Rows[i]["ContactPerson"].ToString(),
                    dataTable.Rows[i]["ContactTel"].ToString(),
                    dataTable.Rows[i]["ProvinceID"].ToString(),
                    dataTable.Rows[i]["WorkLocation"].ToString(),
                    dataTable.Rows[i]["ClosedDate"].ToString(),
                    dataTable.Rows[i]["JobSummary"].ToString(),
                    dataTable.Rows[i]["JobSkills"].ToString(),
                    dataTable.Rows[i]["RequiredNumber"].ToString(),
                    dataTable.Rows[i]["WorkingTypeID"].ToString(),
                    dataTable.Rows[i]["ExperienceLevelID"].ToString(),
                    dataTable.Rows[i]["YearExperience"].ToString(),
                    dataTable.Rows[i]["SalaryFrom"].ToString(),
                    dataTable.Rows[i]["SalaryTo"].ToString(),
                    dataTable.Rows[i]["Currency"].ToString(),
                    dataTable.Rows[i]["ShowSalary"].ToString(),
                    dataTable.Rows[i]["SalaryNegotive"].ToString()};
                    uploadedRecords += sqlServer.ExecuteNonQuery();

                }
                sqlServer.CloseConnection();
            }

            return uploadedRecords == totalRecords;
        }
    }
}
